package org.lq.student.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.student.dao.StudentWriteGradeDao;
import org.lq.student.entity.StudentWriteGrade;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.List;

/**
 * @author haha
 * @create 2020-10-13 18:32
 */
@Log4j
public class StudentWriteGradeDaoImpl implements StudentWriteGradeDao {
    /**
     * 添加
     * @param studentWriteGrade
     * @return
     */
    @Override
    public int save(StudentWriteGrade studentWriteGrade) {
        log.info("开始添加学生成绩...");
        QueryRunner queryRunner=new QueryRunner(JDBCUtil.getDataSource());
        int num=0;
        try {
            num = queryRunner.update("insert into student_write_grate (student_id,staff_id,write_grade_subject,write_grade,write_grade_time,write_grade_note)values(?,?,?,?,now(),?)",
                    studentWriteGrade.getStudentId(),
                    studentWriteGrade.getStaffId(),
                    studentWriteGrade.getWriteGradeSubject(),
                    studentWriteGrade.getWriteGrade(),
                    studentWriteGrade.getWriteGradeNote());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        log.info("添加学生成绩完成，结果为："+num);
        return num;
    }

    /**
     * 修改
     * @param studentWriteGrade
     * @return
     */
    @Override
    public int update(StudentWriteGrade studentWriteGrade) {
        log.info("开始修改学生成绩...");
        QueryRunner queryRunner=new QueryRunner(JDBCUtil.getDataSource());
        int num=0;
        try {
             num= queryRunner.update("update student_write_grade set student_id=?,staff_id=?,write_grade_subject=?,write_grade=?,write_grade_time=now(),write_grade_note=? where write_grade_id=?",
                    studentWriteGrade.getStudentId(),
                    studentWriteGrade.getStaffId(),
                    studentWriteGrade.getWriteGradeSubject(),
                    studentWriteGrade.getWriteGrade(),
                    studentWriteGrade.getWriteGradeNote(),
                    studentWriteGrade.getWriteGradeId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        log.info("修改学生成绩完成，结果为："+num);
        return num;
    }

    /**
     * 删除学生成绩
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        log.info("开始删除学生成绩...");
        QueryRunner queryRunner=new QueryRunner(JDBCUtil.getDataSource());
        int num=0;
        try {
             num= queryRunner.update("delete from student_write_grade where write_grade_id=?", id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        log.info("删除学生成绩完成，结果为："+num);
        return num;
    }

    /**
     * 根据学生Id查询
     * @param id
     * @return
     */
    @Override
    public StudentWriteGrade getById(int id) {
        log.info("开始获取学生成绩id...");
        QueryRunner queryRunner=new QueryRunner(JDBCUtil.getDataSource());
        StudentWriteGrade writeGrade=null;
        try {
             writeGrade= queryRunner.query("select * from student_write_grade_view where write_grade_id=?", new BeanHandler<>(StudentWriteGrade.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        log.info("获取学生成绩id完成，结果为："+writeGrade);
        return writeGrade;
    }

    /**
     *获取学生成绩表的总行数
     * @return
     */
    @Override
    public int getCount() {
        log.info("开始获取学生成绩总行数...");
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        int num=0;
        try {
            Long num1 = queryRunner.query("select count(1) from student_write_grade_view", new ScalarHandler<Long>());
            num=num1.intValue();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

    /**
     * 分页查询
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<StudentWriteGrade> pageList(int startIndex, int pageSize) {
        log.info("开始分页查询学生成绩...");
        QueryRunner queryRunner=new QueryRunner(JDBCUtil.getDataSource());
        List<StudentWriteGrade> list=null;
        try {
            list= queryRunner.query("select * from student_write_grade_view limit ?,?", new BeanListHandler<>(StudentWriteGrade.class), startIndex, pageSize);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        log.info("分页查询学生成绩完成..."+list);
        return list;
    }

    /**
     * 模糊查询总行数
     * @param values
     * @return
     */
    @Override
    public int getCount(String values) {
        log.info("开始模糊查询学生成绩的总行数...");
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        int num=0;
        try {
            Long num1 = queryRunner.query("select count(1) from student_write_grade_view INNER JOIN student_info ON student_write_grade_view.studentId=student_info.student_id where student_name like ?", new ScalarHandler<Long>(),"%"+values+"%");
            num=num1.intValue();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        log.info("模糊查询学生成绩的总行数完成，结果为："+num);
        return num;
    }

    /**
     * 分页模糊查询
     * @param startIndex
     * @param pageSize
     * @param value
     * @return
     */
    @Override
    public List<StudentWriteGrade> pageByValues(int startIndex, int pageSize, String value) {
        log.info("开始分页模糊查询学生成绩...");
        QueryRunner queryRunner=new QueryRunner(JDBCUtil.getDataSource());
        List<StudentWriteGrade> list=null;
        try {
            list= queryRunner.query("select * from student_write_grade_view INNER JOIN student_info ON student_write_grade_view.studentId=student_info.student_id where student_name like ? limit ?,?", new BeanListHandler<>(StudentWriteGrade.class), "%"+value+"%",startIndex, pageSize);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        log.info("分页模糊查询学生成绩结束..."+list);
        return list;
    }
}
